package edu.zut.cs.network.bank.user.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;

import org.apache.log4j.Logger;
//import org.apache.logging.log4j.LogManager;
//import org.apache.logging.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import edu.zut.cs.network.bank.user.dao.UserDao;
import edu.zut.cs.network.bank.user.dao.impl.UserDaoImpl;
import edu.zut.cs.network.bank.user.entity.User;
import edu.zut.cs.network.bank.user.service.UserManager;


public class UserManagerImpl implements UserManager{
//	protected Logger logger = LogManager.getLogger(this.getClass().getName());
	private static Logger logger = Logger.getLogger(UserManagerImpl.class);
	
	Connection conn;
	PreparedStatement st;
	ResultSet rs;
	private String driver;
	private String url;
	private String user;
	private String pass;

	public UserManagerImpl() {
		try {
			this.initParam("mysql.ini");
		} catch (FileNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
			try {
				Class.forName(driver);
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			conn = DriverManager.getConnection(url, user, pass);
			st = conn.prepareStatement("INSERT INTO USER VALUE(NULL,?,?)");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void initParam(String paramfile) throws FileNotFoundException, IOException {
		Properties props = new Properties();
		props.load(new FileInputStream(paramfile));
		driver = props.getProperty("driver");
		url = props.getProperty("url");
		user = props.getProperty("user");
		pass = props.getProperty("pass");
	}

	
	
	List<User> userList;

	UserDao userDao = new UserDaoImpl();

	public void load(String filename) throws FileNotFoundException {

		File file = new File(filename);
		InputStream input = new FileInputStream(file);
		XSSFWorkbook wb = null;
		try {
			wb = new XSSFWorkbook(input);
		} catch (IOException e) {
			e.printStackTrace();
		}
		XSSFSheet sheet = wb.getSheetAt(1);
		try {
			for (int i = 1; i <= sheet.getLastRowNum(); i++) {
				Row row = sheet.getRow(i);
				for (int j = 0; j < row.getLastCellNum(); j++) {
					Cell cell = row.getCell(j);
					if (cell != null) {
						cell.setCellType(CellType.STRING);
						String value = String.valueOf(row.getCell(j).getStringCellValue());
						if (j == 0)
							st.setString(j+1, value);
						if (j == 1)
							st.setString(j+1, value);
					}
				}
				st.executeUpdate();				
			}
			UserManagerImpl.logger.info("导入数据库成功！");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (st != null) {
					st.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

//	public List<User> getAllusers() {
//		// TODO Auto-generated method stub
//		return null;
//	}

	public int addUser(User user) {
		return this.userDao.addUser(user);
	}

	public int updateUser(User user) {
		return this.userDao.updateUser(user);
	}

	public int deleteUser(Long  id) {
		return this.userDao.deleteUser((long)id);
	}

	public User getUserById(Long  id) {
		return this.userDao.getUserById((long)id);
	}

	public User findbyId(Long id) {
		// TODO Auto-generated method stub
		return null;
	}
	
	public static void main(String[] args) throws FileNotFoundException {
		UserManagerImpl userManagerImpl = new UserManagerImpl();
		String user_data_file = "user.xlsx";
		userManagerImpl.load(user_data_file);
	}
	
}
